Method and system in an electronic spreadsheet for managing and handling user-defined options

ABSTRACT

The present invention relates to the field of information processing by digital computers, and more particularly to a method and system of processing user defined boolean variables in a multi dimensional spreadsheet comprising a plurality of cells identified by a cell address along each dimension, said method comprising the steps of:  
     defining one or a plurality of boolean variables in a table;  
     referencing the one or plurality of boolean variables in one or a plurality of cells;  
     activating one or plurality of boolean variables in the table;  
     determining the content of the cell or plurality of cells depending on whether the one or plurality of boolean variables are activated or not in the table.

TECHNICAL FIELD OF THE INVENTION

[0001] The present invention relates to the field of informationprocessing by digital computers, and more particularly to a method andsystem, in an electronic spreadsheet, for managing and handlinguser-defined options.

BACKGROUND ART

[0002] Before computers, numerical analyses, particularly financialones, were usually prepared on an accountant's columnar pad orspreadsheet, with pencil and calculator in hand. By organising data intocolumns and rows, spreadsheets afford the rapid assimilation ofinformation by a reader. The task of preparing a spreadsheet on paper,however, is not quite so fast. Instead, the process tends to be veryslow, as each entry must be tediously calculated and entered into thespreadsheet. Since all calculations are the responsibility of thepreparer, manually prepared spreadsheets are also prone to errors.Hence, preparation of spreadsheets by hand is slow, tedious, andunreliable.

[0003] With the advent of microcomputers, a solution was forthcoming inthe form of “electronic spreadsheets.” Better known simply as“spreadsheets,” these software programs provide a computerisedreplacement for the traditional financial modelling tools: theaccountant's columnar pad, pencil, and calculator. In some regards,spreadsheet programs are to those tools what word processors are totypewriters. Spreadsheets offer dramatic improvements in ease ofcreating, editing, and using financial models.

[0004] A typical spreadsheet program configures the memory of a computerto resemble the column/row or grid format of an accountant's columnarpad, thus providing a visible calculator for a user. Because this “pad”exists dynamically in the computer's memory, however, it differs frompaper pads in several important ways. Locations in the electronicspreadsheet, for example, must be communicated to the computer in aformat which it can understand. A common scheme for accomplishing thisis to assign a number to each row in a spreadsheet, a letter to eachcolumn, and another letter to each sheet (or page) of the spreadsheet.To reference a location at column A and row 1 of the second page (i.e.,the upper-left hand corner), for example, the user types in “B:A1”. Inthis manner, the spreadsheet defines an addressable storage location or“cell” at each intersection of a row with a column within a given page.

[0005] Data entry into an electronic spreadsheet occurs in much the samemanner that information would be entered on an accountant's pad. After ascreen cursor is positioned at a desired location, the user can enteralphanumeric information. Besides holding text and numeric information,however, spreadsheet cells can store special instructions or “formulas”specifying calculations to be performed on the numbers stored inspreadsheet cells. Such spreadsheet cells can also be defined and namedas a range as long as they are arranged as a convex set of cells. Atypical example of such a named range simply corresponds to a regulartable found in an accountant's pad. In this fashion, range names canserve as variables in an equation, thereby allowing precise mathematicalrelationships to be defined between cells. The structure and operationof a spreadsheet program, including advanced functions such as functionsand macros, are documented in the technical, trade, and patentliterature. For an overview, see e.g., Cobb, S., Using Quattro Pro2,Borland-OsbomeIMcCraw-Mll, 1990; and LeBlond, G. and Cobb, D.,

[0006] Using 1-2-3, Que corp., 1985. The disclosures of each of theforegoing are hereby incorporated by reference.

[0007] Electronic spreadsheets offer many advantages over their papercounterparts. For one, electronic spreadsheets are much larger (i.e.,hold more information) than their paper counterparts; electronicspreadsheets having thousands or even millions of cells are notuncommon. Spreadsheet programs also allow users to perform “what-if”scenarios. After a set of computational relationships has been enteredinto the worksheet, thanks to imbedded formulas for instance, theinformation can be recalculated using different sets of assumptions. Theresults of each recalculation appears almost instantaneously. Performingthis operation manually, with paper and pencil, would require therecalculation of every relationship in the model for each change made.Electronic spreadsheet systems have been invented to solve these“what-if” problems : changing an input and seeing what happens to anoutput. “What-if” problems can be formally represented by the definitionof one or several user-defined options, each of them representing anassumption which can either be set as “True” or “False”. The effect of asingle given user defined option can take different forms and requiresthat the spreadsheet user formally represents this effect thanks todifferent spreadsheet built-in means. With the spreadsheet technologiescurrently used , such spreadsheet means can be based on the writing ofsome spreadsheet formulas (requiring thus some in-depth knowledge of theformula language and syntax), or can also be based on the utilization ofso-called “versions”. In both cases, there are several limitations whichcan turn these spreadsheet means into inefficient and error-pronesolutions.

[0008] When relying on spreadsheet formulas, the user needs first tomaster the spreadsheet formula language, something which is by far notan easy task for somebody not used to programming languages. Then theuser must define by himself some formal representation of theuser-defined options, with the associated means for managing them: thissecond task is even more difficult as the user cannot rely on anystringent set of rules (as the ones implemented in a language compileror interpreter) to determine if his work is error-free. Furthermore anelectronic spreadsheet prepared by a given user with his or her own wayof representing options will be difficult to be used by another user ifthe latter has not received precise instructions from the former on theway to handle the options. In short, unless mastering advancedprogramming skills, it is virtually impossible for a regular spreadsheetuser to realise and share error-free “what-if” scenario thanks touser-defined options, by solely relying on the spreadsheet built-informula language.

[0009] Current spreadsheet tools implement today the concept of versionsand version groups, which represent some advantages with respect to theprevious approach. Nevertheless using versions presents also somelimitations, as outlined hereafter. With conventional electronicspreadsheets, versions are associated to ranges of cells. Once a rangeof cell is “versionned”, the user can defined several versions for thisrange. In the classical case where multiple options must be managed, thenumber of versions to be defined may become excessive. Indeed if anelectronic spreadsheet must address a set of N independent options, anycell whose content depends on these N options should be represented with2^(N)versions, each of them corresponding to a given combination ofthese N options. Besides the resulting increase in file and memorystorage (leading to degraded performances), this situation may becomealmost unmanageable for the user, specially in the case where multipledispersed cells are versionned, even with the concept of version groupsallowing to associate versions on different ranges of cells.

SUMMARY OF THE INVENTION

[0010] The present invention relates to the field of informationprocessing by digital computers, and more particularly to a method andsystem of processing user defined boolean variables in a multidimensional spreadsheet comprising a plurality of cells identified by acell address along each dimension, said method comprising the steps of:

[0011] defining one or a plurality of boolean variables in a table;

[0012] referencing the one or plurality of boolean variables in one or aplurality of cells;

[0013] activating one or plurality of boolean variables in the table;

[0014] determining the content of the cell or plurality of cellsdepending on whether the one or plurality of boolean variables areactivated or not in the table.

BRIEF DESCRIPTION OF THE DRAWINGS

[0015] The novel and inventive features believed characteristics of theinvention are set forth in the appended claims. The invention itself,however, as well as a preferred mode of use, further objects andadvantages thereof, will best be understood by reference to thefollowing detailed description of an illustrative detailed embodimentwhen read in conjunction with the accompanying drawings, wherein :

[0016]FIG. 1A is a block diagram of a computer system in which thepresent invention may be embodied.

[0017]FIG. 1B is a block diagram of a software system including anoperating system, an application software, and a user interface forcarrying out the present invention.

[0018]FIG. 1C illustrates the basic architecture and functionality of agraphical user interface in which the present invention may be embodied.

[0019]FIG. 2A shows a spreadsheet notebook interface according to thepreferred embodiment of the present invention.

[0020]FIG. 2B shows the toolbar component of the notebook interfaceshown in FIG. 2A.

[0021]FIGS. 2C and 2D show page identifiers for rapidly accessing andmanipulating individual pages of the notebook interface shown in FIG.2A.

[0022]FIG. 3 illustrates a preferred spreadsheet user interface formanaging user defined options, according to the present invention.

[0023]FIG. 4 illustrates the structure of the Option Table in thepreferred embodiment of the present invention

[0024]FIG. 5 is a flow chart illustrating a preferred method formanaging user-defined options according to the present invention.

[0025]FIG. 6 is a simplified flow chart illustrating the system andmethod according to the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT System Hardware

[0026] As shown in FIG. 1A, the present invention may be embodied on acomputer system 100 comprising a central processor 101, a main memory102, an input/output controller 103, a keyboard

[0027]104, a pointing device 105 (e.g., mouse, track ball, pen device,or the like), a display device 106, and a mass storage 107 (e.g., harddisk). Additional input/output devices, such as a printing device 108,may be included in the system 100 as desired. As illustrated, thevarious components of the system 100 communicate through a system bus110 or similar architecture. In a preferred embodiment, the computersystem 100 includes an IBM-compatible personal computer, which isavailable from several vendors (including International BusinessMachine-IBM Corporation of Armonk, N.Y.).

[0028] Illustrated in FIG. 1B, a computer software system 150 isprovided for directing the operation of the computer system 100.Software system 150, which is stored in system memory 102 and on diskmemory 107, includes a kernel or operating system 151 and a shell orinterface 153. One or more application programs, such as applicationsoftware 152, may be “loaded”0 (i.e., transferred from storage 107 intomemory 102) for execution by the system 100. The system 100 receivesuser commands and data through user interface 153; these inputs may thenbe acted upon by the system 100 in accordance with instructions fromoperating module 151 and/or application module 152. The interface 153,which is preferably a graphical user interface (GUI), also serves todisplay results, whereupon the user may supply additional inputs orterminate the session. In a preferred embodiment, operating system 151and interface 153 are Microsoft Win95, available from MicrosoftCorporation of Redmond, Wash. Application module 152, on the other hand,includes a spreadsheet notebook of the present invention as described infurther detail herein below.

Interface

[0029] A. Introduction

[0030] The following description will focus on the presently preferredembodiments of the present invention, which are embodied in spreadsheetapplications operative in the Microsoft Win95 environment. The presentinvention, however, is not limited to any particular application or anyparticular environment. Instead, those skilled in the art will find thatthe system and methods of the present invention may be advantageouslyapplied to a variety of system and application software, includingdatabase management systems, word processors, and the like. Moreover,the present invention may be embodied on a variety of differentplatforms, including Macintosh, UNIX, NextStep, and the like. Therefore,the description of the exemplary embodiments which follows is forpurposes of illustration and not limitation.

[0031] Referring now to FIG. 1C, the system 100 includes a windowinginterface or workspace 160. Window 160 is a rectangular, graphical userinterface (GUI) for display on screen 106; additional windowing elementsmay be displayed in various sizes and formats (e.g., tiled or cascaded),as desired. At the top of window 160 is a menu bar 170 with a pluralityof user-command choices, each of which may invoke additional submenusand software tools for use with application objects. Window 160 includesa client area 180 for displaying and manipulating screen objects, suchas graphic object 181 and text object 182. In essence, the client areais a workspace or viewport for the user to interact with data objectswhich reside within the computer system 100.

[0032] Windowing interface 160 includes a screen cursor or pointer 185for selecting and otherwise invoking screen objects of interest. Inresponse to user movement signals from the pointing device 105, thecursor 185 floats (i.e., freely moves) across the screen 106 to adesired screen location. During or after cursor movement, the user maygenerate user-event signals (e.g., mouse button “clicks” and “drags”)for selecting and manipulating objects, as is known in the art. Forexample, Window 160 may be closed, re-sized, or scrolled by “clicking”(selecting) screen components 172, 174/5, and 177/8, respectively.

[0033] In a preferred embodiment, screen cursor 185 is controlled with amouse device. Single-button, double-button, or triple-button mousedevices are available from a variety of vendors, including AppleComputer of Cupertino, Calif., Microsoft Corporation of Redmond, Wash.,and Logitech Corporation of Fremont, Calif., respectively. Morepreferably, screen cursor control device 105 is a two-button mousedevice, including both right and left “mouse buttons.”

[0034] Programming techniques and operations for mouse devices are welldocumented in the programming and hardware literature; see e.g.,Microsoft Mouse Programmer's Reference, Microsoft Press, 1989. Thegeneral construction and operation of a GUI event-driven system, such asWindows, is also known in the art: see, e.g., Petzold, C., ProgrammingWindows, Second Edition, Microsoft Press, 1990. The disclosures of eachare hereby incorporated by reference.

[0035] B. Preferred interface

[0036] Shown in FIG. 2A, a spreadsheet notebook interface of the presentinvention will now be described The spreadsheet notebook or workbook ofthe present invention includes a notebook workspace 200 for receiving,processing, and presenting information, including alphanumeric as wellas graphic information. Notebook workspace 200 includes a menu bar 210,a toolbar 220, a current cell indicator 230, an input line 231, a statusline 240, and a notebook window 250. The menu bar 210 displays andinvokes, in response to user inputs, a main level of user commands. Menu210 also invokes additional pull down menus, as is known in windowingapplications. Input line 231 accepts user commands and information forthe entry and editing of cell contents, which may include data,formulas, macros, and the like. Indicator 230 displays an address forthe current cursor (i.e., active cell) position, or the address or nameof a selected named range (i.e. active selection) . At the status line240, system 100 displays information about the current state of theworkbook; for example, a “READY” indicator means that the system isready for the user to select another task to be performed.

[0037] The toolbar 220, shown in further detail in FIG. 2B, comprises arow or palette of tools which provide a quick way for the user to choosecommonly-used menu commands or properties. In an exemplary embodiment,toolbar 220 includes file manipulation buttons 221, printing buttons222, an undo button 223, cut, copy, and paste buttons 224, informationpop-up window buttons tool 225, a named range selection button 226, astyle copy button 227, a column re-sizing button 228, and a sum button229. The functions of these buttons are suggested by their names. Forinstance, buttons 224 cut, copy and paste data and objects to and fromWindows' clipboard. The same actions are also available as correspondingcommands in the Edit menu (available from menu bar 210).

[0038] The notebook, which provides an interface for entering anddisplaying information of interest, includes a plurality of spreadsheetpages. Each page may include conventional windowing features andoperations, such as moving, re-sizing, and deleting. In a preferredembodiment, the notebook includes 256 spreadsheet pages, all of whichare saved as a single disk file on the mass storage 107. Workspace 200may display one or more notebooks, each sized and positioned (e.g.,tiled, overlapping, and the like) according to user-specifiedconstraints.

[0039] Each spreadsheet page of a notebook includes a 2-D spread. Page Afrom the notebook 200, for example, includes a grid in row and columnformat, such as row 3 and column F. At each row/column intersection, abox or cell (e.g., cell C4) is provided for entering, processing, anddisplaying information in a conventional manner. Each cell isaddressable, with a selector being provided for indicating a currentlyactive one (i.e., the cell that is currently selected).

[0040] As shown in FIGS. 2C-D, individual notebook pages are identifiedby page identifiers 260, preferably located along one edge of a DOnotebook. In a preferred embodiment, each page identifier is in the formof a tab member (e.g., members 261 a, 262 a, 263 a) situated along a topedge of the notebook. Each tab member may include representativeindicia, such as textual or graphic labels, including user selectedtitles representing the contents of a corresponding page. In FIG. 2C,the tab members 260 are set to their respective default names. Forexample, the first three tab members (members 261 a, 262 a, 263 a) arerespectively set to A, B, and C. Tab members are typically givendescriptive names provided by the user, however. As shown in FIG. 2D,for example, the first three tab members have now been set to “Contents”(tab member 261 b), “Summary” (tab member 262 b), and “Jan” (tab member263 b). In a similar manner, the remaining tabs are set to subsequentmonths of the year. In this manner, the user associates the pageidentifiers with familiar tabs from an ordinary paper notebook. Thus,the user already knows how to select a page or spread of interest:simply select the tab corresponding to the page (as one would do whenselecting a page from a paper notebook).

[0041] In addition to aiding in the selection of an appropriate page ofinformation, the user-customizable page identifiers serve aid in theentry of spreadsheet named range addresses. For example, when entering aformula referring to a named range of cells on another page, the usermay simply use the descriptive page name in the named range address,thus making it easier for the user to understand the relationship of thecell(s) or information being referenced.

[0042] A general description of the features and operation of thespreadsheet notebook interface may be found in Quattro Pro for Windows(Getting Started, User's Guide and Building Spreadsheet Applications),available from Borland International.

Management Of User-Defined Options

[0043] A. Introduction

[0044] As the power of spreadsheet environments has increased sinceseveral years, it is today possible to develop complex customapplications solely based on spreadsheets, as opposed to applicationsdeveloped with general purpose programming languages like C++ orVisualBasic from Microsoft Corporation. This can be achieved thanks tospreadsheet imbedded tools such as macro languages, script languages,formulas and versions. In typical spreadsheet based applications, it iscommon to find individual cells or ranges of multiple cells whosecontent depends on one or several conditions. Running “what-if” scenariocan therefore be seen as applying different sets of conditions to suchcondition dependent cells. With conventional electronic spreadsheettools, this can be achieved thanks to the concept of version. A versionis always associated to a range of cells (whether it contains a singlecell or multiple cells) and can be seen as a set of mutually exclusiveinstances of this range of cells. When multiple independent conditionshave to be taken into account for running the “what-if” scenario, theresulting number of versions to be defined varies exponentially with thenumber of conditions. Indeed with N independent assumptions, there are2^(N)different combinations of these N assumptions. With N only equal to7, 2^(N) is already equal to 128. Thus even with a small number ofconditions, there is a quite large number of condition combinations,which can easily reach the limit of the spreadsheet. Furthermore whendifferent cells dispersed within an electronic spreadsheet, depend onthe same set of conditions, conventional electronic spreadsheet toolspropose to use the concept of version groups. Version groups requirefrom the spreadsheet user a careful definition of the dependenciesbetween the versions of the different cell ranges.

[0045] The present invention offers a user-friendly solution to thisproblem by defining a method and a system for managing user-definedoptions.

[0046] In the next sections, the above-mentioned conditions will bereferred to as options. An option is defined as a boolean variable,which can be set as “True” or “False” and which may impact the contentof any given cell within an electronic spreadsheet, by referencing itjust as a conventional named range. For instance the formula “$baseprice*(1-10%*$discount)” refers on one hand to a conventional named range“baseprice” and on the other hand to a named range “discount” which isalso defined as an option according to the present invention. In thisexample, when the option “discount” is “false” (with option value“false” conventionally set to 0), the formula takes the same value asthe one contained in the named range “baseprice”. Alternatively, whenthe option “discount” is “true” (with option value “true” conventionallyset to 1), the formula results in a value equal to the value of thenamed range “baseprice”, decreased by 10%.

[0047] B. Option Manager

[0048] In contrast to just-described conventional tools, the presentinvention provides a more powerful, user-friendly and interactiveapproach for managing user-defined options in the form of an “OptionManager”. The Option Manager automatically allows the electronicspreadsheet user

[0049] to define if a given condition deserves to be handled as aso-called Option, and afterwards

[0050] to manage this option.

[0051] In a preferred embodiment, the present invention is used in threesteps

[0052] 1. The first step occurs when the spreadsheet user decides, basedon some criteria not detailed here, if one or several conditions deserveto take advantage of the present invention, that is to be managed as oneor several options by the Option Manager.

[0053] The user first invokes a specific command called “Option_Manager”thanks to conventional means available in spreadsheet environment, suchas (but not limited to) dedicated push-buttons, keyboard entry shortcuts, menu or sub-menu entries. This results in displaying on theDisplay device 106 an Option Manager Dialog Box 300, as shown by FIG. 3.Within this Option Manager Dialog Box 300, the user can visualisealready defined options in the “List Box” 301 (such as the ones named“year end”, “volume”, “new customer” and “rebate” as shown in FIG. 3),as well as unused options whose names follow some predefined template,such as “option5” where the character “*” is a wild card forrepresenting numbers. In the example shown in FIG. 3, the bottom optionwithin the List Box 301 is named “option5” and therefore corresponds inthis preferred embodiment as an unused option. Any other similar ordifferent naming convention may be used without departing from thespirit of this invention. To visualise any other options, whetheralready defined or spare, not displayed within the list box 301 of theOption Manager Dialog Box 300, the user can for instance use thepointing device 105 to click on the scroll bar 302, so that the list box301 can move upwards on downwards along the full set of used and spareoptions. Then the user typically decides to use the first unused option(“option5” as shown in the list box 301 within the Option Manager DialogBox 300) for becoming the first new used option. For this purpose theuser uses the pointing device 105 to click on the push-button “Rename”305 located on the right of the “option5” element of the list box 301.This result in displaying on the display device 106 a new dialog box310. Within this dialog box 310, a user entry field 311 allows the userto change with the keyboard 104 the default option name “option5” into anew one. If at that point the user decides, for any reason not detailedhere, not to define a new option, it can cancel this operation byclicking on the push-button “Cancel” 313. This will result in closingthe dialog box 310 from the display device 106 and then giving backcontrol to the Option Manager dialog box 300. Alternatively, if the userwants to continue with the new option definition, it confirms theoperation by clicking on the push-button “OK” 313 within the dialog box310. This will result in closing the dialog box 310 from the displaydevice 106 and then giving back control to the Option Manager dialog box300. which now shows in the bottom of the list box 301 the new name ofthe just specified option. If the user wants to defined other newoptions, it can follow the same steps as long as spare options are leftunused. When done, the user uses the pointing device 105 to click on thepush-button “Done” 306. This will result in closing the Option ManagerDialog Box 300 on the display device 106.

[0054] 2. The second step occurs when the spreadsheet user decides,based on his or her own criteria not detailed here, to take advantage ofthe present invention by manipulating already defined options thanks tothe Option Manager. Such manipulation can either be to rename one orseveral already defined options, or to read and/or to change the status(between the “True” and “False” status) of one or a plurality of alreadydefined options.

[0055] In both cases, the user first invokes a specific command called“Option_Manager” thanks to conventional means available in thespreadsheet environment, such as (but not limited to) dedicatedpush-buttons, keyboard entry short cuts, menu or sub-menu entries. Thisresult in displaying on the display device 106 an Option Manager DialogBox 300, as shown by FIG.

[0056] 3. Within this Option Manager Dialog Box 300, the user canvisualise already defined options in the “List Box” 301 (such as theones named “year end”, “volume”, “new customer” and “rebate” as shown inFIG. 3). To visualise any other defined options possibly not displayedwithin the list box 301 of the Option Manager Dialog Box 300, the usercan for instance use the pointing device 105 to click on the scroll bar302, so that the list box 301 can move upwards on downwards along thefull set of used and spare options. If the user choice is to rename oneor several already defined options, then he/she has to follow, for eachrelevant options, a sequence of steps similar to the ones used toinitially define a new option. In short it consists in first using thescroll bar 302 to display within the list box 301 the option to rename,then to click on the push-button “Rename” standing on the right of theselected option (as the push-button 305 if the selected option appearsat the bottom of the list box 301), then to replace by using thekeyboard 104 within the displayed dialog box 310 the current option namedisplayed in the window 311, and then to click on the push-button “OK”312. If the user choice is to read and/or change the status of one orseveral defined options, he/she begins as above to display within thelist box 301 (possibly by using the scroll bar 302) the first optionwhose status must be read and/or updated. Once done, the status of thisoption is shown in the label box sitting immediately on the right of thelist box 301. For instance if the currently managed option is the bottomone within the list box 301, then this status information is shown inthe label box 303; it can take the values “TRUE” or “FALSE”. If the userdecides, for any reason not detailed here, to change the current statusfrom “TRUE” to “FALSE” or conversely from “FALSE” to “TRUE”, then theuser must click with the pointing device 105 on the push-button “Change”304. The effect of this operation is reflected within the Option ManagerDialog Box 300 by swapping the “TRUE” and “FALSE” values shown in thelabel box 304. All these steps must be repeated for every option forwhich the user wishes to read and/or update the status. When done, theuser uses the pointing device 105 to click on the push-button “Done”306. This will result in closing the Option Manager Dialog Box 300 onthe display device 106.

[0057] 3. The third step occurs when the spreadsheet user decides, basedon his or her own criteria not detailed here, whether the content of agiven cell must depend or not on a given specified option. If it is thecase, then the option can be referenced within the target cell contentas a classical named range, as conventionally done within electronicspreadsheets with the classical rule that the “FALSE” value correspondsto the value 0 and that the “TRUE” value corresponds to the value 1. Letillustrate this by one example. It is assumed that the user relies onhis/her electronic spreadsheet to compute the price of an item, and thatthe pricing structure allows both to apply a 10% discount if the item issold during the year end promotion period, and to apply a 100$ rebatefor fidelity club members. Let further assume that the item base priceequal to 1500$ is contained within a cell which has received the name“baseprice”, that the spreadsheet user has defined an option named “yearend” to reflect if the year end promotion can be applied or not, andthat the spreadsheet user has defined an option named “rebate” toreflect if the fidelity club rebate can be applied or not. Then thespreadsheet user can get the item customer price by filling the relevantcell of his/her spreadsheet with a formula taking the following form:“$baseprice*(1-10%*$year end)-$rebate1500”. When the electronicspreadsheet user takes advantage of the present invention by invokingthe Option_Manager command for changing the status of the two options“year end” and “rebate” as described above in the second step, then thecell containing the above formula with show the values 1500$, 1350$,1400$ and 1250$ when the option pair (“year end”, “rebate”) respectivelytakes the values (FALSE, FALSE), (TRUE, FALSE), (FALSE, TRUE) and (TRUE,TRUE).

[0058] C. Option Table

[0059] The decision to define a new option, or to rename an existingoption, or to change the status of an existing belongs to the user ofthe spreadsheet application. A single repository, called “Option Table”,is used to record this information. This Option Table is preferablysaved on a non volatile memory (typically but not necessary as part ofthe spreadsheet disk file on the mass storage 107).

[0060] Referring now to FIG. 4, the Option Table 400 corresponds to alogical simple structure made of several records 401, each of themassociated with an option. Each record includes three fields:

[0061] An “Option Index” field 402 is used for sorting the option table.When options are displayed within the list box 301 of the Option ManagerDialog Box 300, they are presented according to the index sequence,starting at the last recorded position.

[0062] An “Option Name” field 403 is used to record the name of theoption of record 401. As already discussed, the default value of anunused option can follow a template such as “option*”. For optionsdefined by the spreadsheet user, the option name field 403 records theuser specified name, as it was entered by the spreadsheet user thanks tothe window 311 of the dialog box 310.

[0063] An “Option Status” field 404 is used to record the current status(either “TRUE” or “FALSE”) of the option of record 401. This informationis reflected in the label box 303 of the Option Manager Dialog Box 300.

[0064] C. Option_Manager method

[0065] The method of managing user-defined options is summarised in FIG.6:

[0066] At step 601, the method is waiting for a user request to displayon the display device 106 an Option Manager Dialog Box (OMDG) 300.

[0067] At step 602, a user request for displaying the OMDG 300 on thedisplay device 106 is detected.

[0068] At step 603, the OMDG 300 is displayed on the display device 106.The option attributes (names and status) are shown in relevant windowsas recorded in the option table 400.

[0069] At step 604, the method is waiting for a user action on the OMDC300.

[0070] At step 605, a user action on the OMDG 300 for eithermanipulating the options (creating a new option, renaming an existingoption, changing the status of an existing option), or for closing theOMDG 300 is detected. In the former case, control is given to step 606,in the later case control is given to step 607.

[0071] At step 606, the OMDG is closed on the display device 106 andcontrol is given back to the initial step 601 for treating any newfuture request.

[0072] At step 607, the option table 400 is updated to reflect thechange on the option, as detected during the step 605.

[0073] At step 608, every cell of the spreadsheet whose content dependson the user-defined options is updated according to the former optiontable change. Then control is given back to the step 603 to display inthe OMDG the result of the user action which was detecting at step 605.

[0074] The method of handling user-defined options to take advantage ofthe present invention is detailed in flowchart 500 of FIG. 5. Thismethod can be seen as the processing of the Option_Manager command usedfor defining a new option, or renaming an existing option, or changingthe status of an existing option. The method comprises the followingsteps :

[0075] At step 501, the method is in its default state, waiting for anevent to initiate the process.

[0076] At step 502, an event is detected, as a result of a user action.This action can be for instance a specific combination of key on thekeyboard 104, or the click of the pointing device 105 on a specificbutton, or any other similar means not further specified here.

[0077] At step 503, a local variable named “StartIndex” is initialisedwith the value of another local variable named “LastStartIndex”. Thislater variable is used to record the option index 402, within the optiontable 400, of the option which was displayed at the top of the list box301 within the is Option Manager Dialog Box 300. The two variablesStartIndex and LastStartIndex are recorded with classical means, such asbut not limited to the spreadsheet disk file on the mass storage 107.

[0078] At step 504, the Option Manager Dialog Box 400 is displayed onthe display device 106. The option name appearing on the top of the listbox 301 corresponds to the option name field 403 of the record 401 inthe option table 400 whose option index field 402 is equal to thevariable StartIindex. Underneath option names within this list box 301correspond to the option name fields 403 of the following records 401 ofthe option table 400. The values “TRUE” or “FALSE” displayed in thelabel boxes like 303 correspond to the option status fields 404 found inthe option table 400 for the same records 401.

[0079] At step 505, the method is waiting for any user action on theOption Manager Dialog Box 400. Such user action is typically resultingfrom a click with the pointing device 105, but take other similar formssuch as, but not limited to a specific combination of key on thekeyboard 104, or any other similar means not further specified here

[0080] At step 506, a user action on the Option Manager Dialog Box 400is detected. If the user action is a click on the push-button “Done”306, then control is given to step 507; if the user action is a click ona push-button “Change” like 304, then control is given to step 509; ifthe user action is a click on a push-button “Rename” like 305, thencontrol is given to step 510; if the user action is a click on theup-arrow of the scrolling bar 302, then control is given to step 516;and if the user action is a click on down-arrow of the scrolling bar302, then control is given to step 517.

[0081] At step 507, the value of the variable LastStartIndex is setequal to the current value of the variable StartIndex.

[0082] At step 508, the Option Manager Dialog Box 400 is closed, so thatit disappears from the display device 106, and control is given back tothe initial step 501 for treating any future Option Manager_command.

[0083] At step 509, the Option table 400 is updated to modify the optionstatus field 404 for the record 401 whose option name field 403 matchesthe name found in the list box 301 with the same horizontal relativeposition as the clicked “Change” push-button of type 304. This optionstatus field 404 modification corresponds to a swap between the values“TRUE” and “FALSE”. Then control is given to the step 518.

[0084] At step 510, the user is prompted to specify the new name of theselected option. This can be done with conventional means as the Dialogbox 310 which is part of the preferred embodiment, but any functionallyequivalent means can be used instead, without departing from the spiritof the invention.

[0085] At step 511, the new option name specified by the spreadsheetuser is checked against a set of rules not detailed here to determine ifit is or not a valid option name. Such rules are typicallyimplementation dependent and thus do not belong to the scope of thepresent invention. If the result of this checking is that the new optionname is found as valid, then control is given to step 512, otherwisecontrol is given to step 513.

[0086] At step 512, the Option table 400 is updated to modify the optionname field 403 for the record 401 whose former option name field 403matched the previous name which has been updated at step 511. Thencontrol is given to step 518.

[0087] At step 513, an error message notification is issued for warningthe user that the new name specified at step 510 has not passed the namevalidity checking of step 511. This can typically be done by displayingon the display device 106 an error message in a pop-up window, but anyother similar means could be used instead, without departing from thespirit of the present invention.

[0088] At step 514, the method is waiting for a user acknowledgement,meaning that the error message notification of step 513 has beenreceived by the spreadsheet user.

[0089] At step 515, the user acknowledgement is detected. This cantypically correspond to the click, thanks to the pointing device 105, ona “OK” push-button within the pop-up window typically displayed duringthe step 513, but other similar means can be used instead, withoutdeparting from the spirit of the present invention. Then control isgiven to step 518.

[0090] At step 516, the value of the variable StartIndex is decrementedas long as the resulting new value remains positive. Then control isgiven to step 518.

[0091] At step 517, the value of the variable StartIndex is incrementedas long as the resulting new value remains less or equal than a fixedvalue OptionTableSize corresponding to the highest option index 402 of arecord 401 within the option table 400 that can be displayed within thelist box 301 of the Option Manager Dialog Box 300. Then control is givento step 518.

[0092] When the step 518 is reached, the fields 301 (list box containingthe option names) and 303 (labels reflecting the option status) withinthe Option Manager Dialog Box 300 are refreshed on the display device106 to reflect the user actions taken at step 506. Then control is givento step 505.

Alternate Embodiments

[0093] While the invention has been particularly shown and describedwith reference to a preferred embodiment, it will be understood thatvarious changes in form and detail may be made therein without departingfrom the spirit, and scope of the invention.

[0094] The Option_Manager method and system according to the presentinvention may be used advantageously in environments where elements ofinformation are organised as multidimensional tables having more thanthree dimensions.

what is claimed is:
 1. A method of processing user defined booleanvariables in a multi dimensional spreadsheet (200) comprising aplurality of cells identified by a cell address along each dimension,said method comprising the steps of: defining one or a plurality ofboolean variables in a table (400); referencing said one or plurality ofboolean variables in one or a plurality of cells; activating one orplurality of boolean variables in said table (400); determining thecontent of said cell or plurality of cells depending on whether said oneor plurality of boolean variables are activated or not in said table. 2.The method according to the preceding claims wherein said step ofdefining one or a plurality of boolean variables in a table, comprisesthe further steps of: for each defined boolean variable: assigning aname (311) and storing in the table (400) said name (403); selecting astatus value (303) depending on whether the boolean variable isactivated or not and storing in the table said status value (404) . 3.The method according to any one of the preceding claims wherein saidstep of activating one or plurality of boolean variables in said table,comprises the further step of: updating in the table the status value(304, 404) of the one or plurality of activated boolean variables. 4.The method according to any one of the preceding claims wherein the stepof determining the content of said cell or plurality of cells dependingon whether said one or plurality of boolean variables are activated ornot, comprises the further step of: determining, referring to the table(400), whether the one or the plurality of boolean variables referencedin the cell or plurality of cells are activated or not; computing thevalue of said cell or plurality of cells according to the value of saidone or plurality of activated boolean variables.
 5. The method accordingto any one of the preceding claims comprising the further step of:changing in the table (400) the name (311) of one or a plurality ofboolean variables.
 6. The method according to any one of the precedingclaims wherein said step of selecting a status value (303) depending onwhether the boolean variable is activated or not comprises the furthersteps of: setting the status value of the boolean variable to “true” ifsaid boolean variable is activated, and setting the status value of theboolean variables to “false” if said boolean variable is not activated.7. The method according to any one of the preceding claims comprisingthe further step of: setting the value of the boolean variable to onewhen the status value is “true”, or setting the value of the booleanvariable to zero when the status value is “false”.
 8. The methodaccording to any one of the preceding claims wherein said steps ofassigning a name, changing the name, selecting a status value, updatingthe status value are executed by means of an interactive user interface.9. The method according to any one of the preceding claims wherein saidinteractive user interface comprises a dialog box (300) displayed on ascreen (106) of a computer system (100).
 10. A system (100) comprisingmeans adapted for carrying out the method according to any one of thepreceding claims.
 11. A computer program comprising computer readableinstructions adapted for carrying out the method according to claims 1to 9.